﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Omu.ValueInjecter;
using System.Configuration;
using NetWing.Common.Data.SqlServer;


namespace NetWing.Common.Data
{
    public static class DbUtils
    {
        static string cs = SqlEasy.connString; //数据库连接字符串
        public static IEnumerable<T> GetWhere<T>(object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where "
                        .InjectFrom(new FieldsBy()
                        .SetFormat("{0}=@{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                    }
                }
            }
        }

        public static int CountWhere<T>(object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select count(*) from " + TableConvention.Resolve(typeof(T)) + " where "
                        .InjectFrom(new FieldsBy()
                        .SetFormat("{0}=@{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 清空表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static int Delete<T>()
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "TRUNCATE TABLE  " + TableConvention.Resolve(typeof(T));
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }


        public static int Delete<T>(int id)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where KeyID=@KeyID";

                cmd.InjectFrom<SetParamsValues>(new { KeyID = id });
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        public static int DeleteWhere<T>(object where)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where "
                    .InjectFrom(new FieldsBy()
                        .SetFormat("{0}=@{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where);

                cmd.InjectFrom<SetParamsValues>(where);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }


        public static int Delete<T>(string ids)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where charindex(',' + cast(keyid AS varchar(50)) + ',',','  + @KeyID + ',') > 0";

                cmd.InjectFrom<SetParamsValues>(new { KeyID = ids });
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        ///<returns> the id of the inserted object </returns>
        public static int Insert(object o)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                    .InjectFrom(new FieldsBy().IgnoreFields("keyid"), o) + ") values("
                    .InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("@{0}"), o)
                    + ") select @@identity";

                cmd.InjectFrom(new SetParamsValues().IgnoreFields("keyid"), o);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        public static int Insert(object o, string IgnoreFields)
        {
            string[] strarr = { };
            if (!string.IsNullOrEmpty(IgnoreFields))
                strarr = IgnoreFields.Split(',');
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                    .InjectFrom(new FieldsBy().IgnoreFields(strarr), o) + ") values("
                    .InjectFrom(new FieldsBy().IgnoreFields(strarr).SetFormat("@{0}"), o)
                    + ") ";

                cmd.InjectFrom(new SetParamsValues().IgnoreFields(strarr), o);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteNonQuery());
            }
        }

        /// <summary>
        /// 事务方法返回一个sqlscalar 注意查询字段必须唯一且只能为数字
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="transaction">数据库事务</param>
        /// <returns>返回obj可以强制转换为int</returns>
        public static object tranExecuteScalar(string sqlstr, SqlTransaction transaction)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//元数据分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlstr;
            object retval = cmd.ExecuteScalar();
            return retval;
        }



        /// <summary>
        /// 事务方法执行一条sql语句
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public static int tranExecuteNonQuery(string sqlstr, SqlTransaction transaction)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlstr;
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 事务方法插入一条 返回int 插入的KeyId by朱光明
        /// </summary>
        /// <param name="o">object</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public static int tranInsert(object o, SqlTransaction transaction)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                .InjectFrom(new FieldsBy().IgnoreFields("keyid"), o) + ") values("
                .InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("@{0}"), o)
                + ") select @@identity";

            cmd.InjectFrom(new SetParamsValues().IgnoreFields("keyid"), o);
            return Convert.ToInt32(cmd.ExecuteScalar());
        }

        /// <summary>
        /// 事务方法插入一条 返回int 插入的KeyId by朱光明
        /// </summary>
        /// <param name="tranSql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static int tranInsert(string tranSql, SqlTransaction transaction)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = tranSql + "   select @@identity";
            return Convert.ToInt32(cmd.ExecuteScalar());
        }


        /// <summary>
        /// 事务方法更新一条 返回int影响行数 by朱光明
        /// </summary>
        /// <param name="o">object</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public static int tranUpdate(object o, SqlTransaction transaction, string[] IgnoreFields = null)
        {
            if (IgnoreFields == null)
            {
                IgnoreFields = new string[] { "keyid" };//
            }

            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                .InjectFrom(new FieldsBy().IgnoreFields(IgnoreFields).SetFormat("{0}=@{0}"), o)
                + " where KeyID = @KeyID";
            cmd.InjectFrom<SetParamsValues>(o);
            return Convert.ToInt32(cmd.ExecuteNonQuery());
        }


        /// <summary>
        /// 事务方法更新一条 返回int影响行数 by朱光明
        /// </summary>
        /// <param name="o"></param>
        /// <param name="transaction">事务</param>
        /// <param name="IgnoreFields">忽略的字段</param>
        /// <param name="UpdateFields">要更新的字段</param>
        /// <returns></returns>
        public static int tranUpdate(object o, SqlTransaction transaction, string[] IgnoreFields = null, string[] UpdateFields = null)
        {
            if (IgnoreFields == null)
            {
                IgnoreFields = new string[] { "keyid" };//
            }

            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                .InjectFrom(new FieldsBy().IgnoreFields(IgnoreFields).UpdateFields(UpdateFields).SetFormat("{0}=@{0}"), o)
                + " where KeyID = @KeyID";
            cmd.InjectFrom<SetParamsValues>(o);
            return Convert.ToInt32(cmd.ExecuteNonQuery());
        }

        /// <summary>
        /// 事务方法更新一条  只更新想要更新的字段 返回int影响行数 by朱光明
        /// </summary>
        /// <param name="o"></param>
        /// <param name="transaction">事务</param>
        /// <param name="UpdateFields">要更新的字段</param>
        /// <returns></returns>
        public static int tranUpdateOnlyUpdateFields(object o, SqlTransaction transaction, string[] UpdateFields = null)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                .InjectFrom(new FieldsBy().UpdateFields(UpdateFields).SetFormat("{0}=@{0}"), o)
                + " where KeyID = @KeyID";
            cmd.InjectFrom<SetParamsValues>(o);
            return Convert.ToInt32(cmd.ExecuteNonQuery());
        }






        /// <summary>
        /// 事务方法更新一条 返回int影响行数 by朱光明
        /// </summary>
        /// <param name="tranUpdateSql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static int tranUpdate(string tranUpdateSql, SqlTransaction transaction)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = tranUpdateSql;
            return Convert.ToInt32(cmd.ExecuteNonQuery());
        }


        public static int Update(object o)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                    .InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("{0}=@{0}"), o)
                    + " where KeyID = @KeyID";

                cmd.InjectFrom<SetParamsValues>(o);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteNonQuery());
            }
        }
        /// <summary>
        /// 更新一个表 返回影响行数 忽略不更新的字段
        /// </summary>
        /// <param name="o">模型</param>
        /// <param name="fields">忽略不更新的字段</param>
        /// <returns></returns>
        public static int Update(object o, params string[] fields)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                    .InjectFrom(new FieldsBy().IgnoreFields(fields).SetFormat("{0}=@{0}").SetNullFormat("{0}=@{0}"), o)
                    + " where KeyID = @KeyID";
                cmd.InjectFrom<SetParamsValues>(o);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteNonQuery());
            }
        }

        /// <summary>
        /// 更新一条 返回int影响行数 by朱光明
        /// </summary>
        /// <param name="o"></param>
        /// <param name="UpdateFields">需要更新的字段</param>
        /// <returns></returns>
        public static int UpdateOnlyUpdateFields(object o, string[] UpdateFields)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                    .InjectFrom(new FieldsBy().UpdateFields(UpdateFields).SetFormat("{0}=@{0}").SetNullFormat("{0}=@{0}"), o)
                    + " where KeyID = @KeyID";
                cmd.InjectFrom<SetParamsValues>(o);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteNonQuery());
            }
        }

        /// <summary>
        /// 事务方法更新 自由写条件语句 用法大致是 DbUtils.tranUpdateWhatWhere<DemoRuKuDanMingXiModel>(new {rkdId = rkdid});
        /// </summary>

        /// <param name="o"></param>
        /// <param name="where"></param>
        /// <param name="transaction"></param>
        /// <param name="IgnoreFields"></param>
        /// <param name="UpdateFields"></param>
        /// <returns></returns>
        public static int tranUpdateWhatWhere(object o, string where, SqlTransaction transaction, string[] _IgnoreFields = null, string[] UpdateFields = null)
        {
            SqlCommand cmd = transaction.Connection.CreateCommand();//通过transaction的连接Connection创建一个SqlCommand
            cmd.Transaction = transaction;//分配事务 
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                    .InjectFrom(new FieldsBy().IgnoreFields(_IgnoreFields).SetFormat("{0}=@{0}"), o)
                    //.InjectFrom(new FieldsBy().IgnoreFields(IgnoreFields).SetFormat("{0}=@{0}"), o)
                    + " where " + where;
                cmd.InjectFrom<SetParamsValues>(o);
                return cmd.ExecuteNonQuery();
            }
        }


        public static int UpdateWhatWhere<T>(object what, object where)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + TableConvention.Resolve(typeof(T)) + " set "
                    .InjectFrom(new FieldsBy().SetFormat("{0}=@{0}"), what)
                    + " where "
                    .InjectFrom(new FieldsBy()
                    .SetFormat("{0}=@wp{0}")
                    .SetNullFormat("{0} is null")
                    .SetGlue("and"),
                    where);

                cmd.InjectFrom<SetParamsValues>(what);
                cmd.InjectFrom(new SetParamsValues().Prefix("wp"), where);

                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }


        public static int InsertNoIdentity(object o)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                    .InjectFrom(new FieldsBy().IgnoreFields("keyid"), o) + ") values("
                    .InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("@{0}"), o) + ")";

                cmd.InjectFrom<SetParamsValues>(o);

                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        /// <returns>rows affected</returns>
        public static int ExecuteNonQuerySp(string sp, object parameters)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static int ExecuteNonQuery(string commendText, object parameters)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = commendText;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static IEnumerable<T> ExecuteReader<T>(string sql, object parameters) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                }
            }
        }


        public static IEnumerable<T> ExecuteReaderSp<T>(string sp, object parameters) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                }
            }
        }

        public static IEnumerable<T> ExecuteReaderSpValueType<T>(string sp, object parameters)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        while (dr.Read())
                        {
                            yield return (T)dr.GetValue(0);
                        }
                }
            }
        }

        public static int Count<T>()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select count(*) from " + TableConvention.Resolve(typeof(T));
                    conn.Open();

                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static int GetPageCount(int pageSize, int count)
        {
            var pages = count / pageSize;
            if (count % pageSize > 0) pages++;
            return pages;
        }

        public static IEnumerable<T> GetAll<T>() where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T));
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                    }
                }
            }
        }

        public static IEnumerable<T> GetList<T>(string sql, object parameters) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }

                        dr.Close();
                    }


                }
            }
        }

        public static DataTable GetPageWithSp(ProcCustomPage pcp, out int recordCount)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = pcp.Sp_PagerName;
                    cmd.InjectFrom(new SetParamsValues().IgnoreFields("sp_pagername"), pcp);

                    SqlParameter outputPara = new SqlParameter("@RecordCount", SqlDbType.Int);
                    outputPara.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(outputPara);

                    conn.Open();

                    using (var da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        recordCount = PublicMethod.GetInt(outputPara.Value);
                        conn.Close();
                        conn.Dispose();

                        return ds.Tables[0];
                    }
                }
            }
        }

        /// <summary>
        /// 根据条件获取记录
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="sort">排序 如：keyid desc </param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPage<T>(int page, int pageSize, string sort, object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    var name = TableConvention.Resolve(typeof(T));

                    if (string.IsNullOrEmpty(sort))
                        sort = "keyid desc";

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "with result as(select *, ROW_NUMBER() over(order by {3}) nr from {0} where "
                    .InjectFrom(new FieldsBy()
                        .SetFormat("{0}=@{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where) + @" )
                    select  * 
                    from    result
                    where   nr  between (({1} - 1) * {2} + 1)
                            and ({1} * {2}) ";

                    cmd.CommandText = string.Format(cmd.CommandText, name, page, pageSize, sort);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                    }
                }
            }



        }

        /// <summary>
        /// 默认为KEYID 倒序排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPage<T>(int page, int pageSize, string sort = "keyid desc") where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    var name = TableConvention.Resolve(typeof(T));

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(@"with result as(select *, ROW_NUMBER() over(order by {3}) nr
                            from {0}
                    )
                    select  * 
                    from    result
                    where   nr  between (({1} - 1) * {2} + 1)
                            and ({1} * {2}) ", name, page, pageSize, sort);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 根据ID得到一个对象实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyid"></param>
        /// <returns></returns>
        public static T Get<T>(long keyid) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where keyid = " + keyid;
                conn.Open();

                using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    while (dr.Read())
                    {
                        var o = new T();
                        o.InjectFrom<ReaderInjection>(dr);
                        return o;
                    }
            }
            return default(T);
        }

        public static T Get<T>(object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where "
                        .InjectFrom(new FieldsBy()
                        .SetFormat("{0}=@{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where);
                conn.Open();

                using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    while (dr.Read())
                    {
                        var o = new T();
                        o.InjectFrom<ReaderInjection>(dr);
                        return o;
                    }
            }
            return default(T);
        }
    }
}
